package com.googlecode.flightreports.flightlist;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement; 
import java.sql.SQLException;
import java.text.SimpleDateFormat;

import com.googlecode.kohmori.reportgen.AbstractSQLSource;
import com.googlecode.kohmori.springmvc.DefaultReportParams;
import org.xml.sax.InputSource;

/* 
 * This class creates the report object and uses SQL to populate its values.
 */
public class ReportSQL extends AbstractSQLSource { 

    /**
     * Creates a report object
     * @param dbConn Connection object to run SQL
     * @return report instance, null if no report found
     */
    public InputSource createReportObject(Connection dbConn, DefaultReportParams reportParams) 
    throws SQLException {
        ReportParams rP = (ReportParams) reportParams;        
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MMM-dd");
        ReportObject rpt = new ReportObject();
        rpt.setQueryParams(rP);

        try {
            String sqlStatement = 
                "SELECT " +
                "current_timestamp as run_date " +
                "from sysibm.sysdummy1";
            
            PreparedStatement pstmt = dbConn.prepareStatement(sqlStatement);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                rpt.setRunDate(rs.getTimestamp("run_date"));
            } else {
                throw new SQLException("System error: " +
                        "Database access problem");
            }

            rs.close();
          
            sqlStatement = 
                "SELECT flight_number, " +
                "origin, destination, depart_time," +
                "arrive_time, miles, number_of_segments, " +
                "aircraft_type " +
                "from APP.V_FLIGHTLIST " +
                "where airline_name = ? " +
                "order by " + rP.getSortDBColumnName() +
                ", flight_number";
                    
            pstmt = dbConn.prepareStatement(sqlStatement);
            pstmt.setString(1, rP.getAirlineName());
            rs = pstmt.executeQuery();

            while (rs.next()) {
                ReportObject.Item item = new ReportObject.Item();
                item.setFlightNumber(rs.getString("flight_number"));
                item.setOrigin(rs.getString("origin"));
                item.setDestination(rs.getString("destination"));
                item.setDepartTime(rs.getString("depart_time"));
                item.setArriveTime(rs.getString("arrive_time"));
                item.setMiles(rs.getInt("miles"));
                item.setNumberOfSegments(rs.getInt("number_of_segments"));
                item.setAircraftType(rs.getString("aircraft_type"));
                rpt.addItem(item);
            }

            pstmt.close();

        } catch (SQLException e) {
            throw new SQLException("Exception in ReportSQL: " + e.getMessage());
        }
        return rpt;
    }
}
